# 加载必要的库
library(lubridate)
library(readxl)
library(dplyr)

# 1. 建立数据框（或导入问卷，数据框名称改为leadership）
# 假设数据已经以某种方式导入到R中，并且数据框名为leadership
# 如果数据是从Excel导入的，可以使用readxl包
leadership <- read_excel("C://Users//86189//Desktop//CodeOfGitee//r-code//2207124039_毛若沣_02//data.xls")

# 2. 将年龄为99的职工年龄重置为NA
leadership <- leadership %>% mutate(age = ifelse(age == 99, NA, age))

# 3. 按照age规则，为数据框添加新的一列agecat
leadership <- leadership %>% mutate(agecat = case_when(
  age < 35 ~ "young",
  age <= 55 ~ "middle aged",
  TRUE ~ "elder"
))

# 4. 将“date of entry”列修改变量名为“entry”，进而将Q1-Q5的名称修改为item1-item5
names(leadership)[names(leadership) == "date of enter"] <- "entry"
names(leadership)[names(leadership) %in% c("Q1", "Q2", "Q3", "Q4", "Q5")] <- c("item1", "item2", "item3", "item4", "item5")

# 确保列名更改后，再计算seniority
leadership <- leadership %>% mutate(seniority = as.numeric(year(Sys.Date()) - year(entry)))

# 7. 将leadership数据中的“entry”列的数据转化为mm/dd/yy格式
leadership$entry <- format(leadership$entry, "%m/%d/%y")

# 8. 将系统时间以不同格式输出
cat("当前日期和时间:", Sys.time(), "\n")
cat("当前日期（年-月-日）:", format(Sys.Date(), "%Y-%m-%d"), "\n")
cat("当前时间（小时:分钟:秒）:", format(Sys.time(), "%H:%M:%S"), "\n")

# 9. 将leadership数据按年龄排序，按性别和年龄排序
leadership_age_sorted <- leadership %>% arrange(age)
leadership_gender_age_sorted <- leadership %>% arrange(gender, age)

# 读取additon1.xls文件
additon1 <- read_excel("C://Users//86189//Desktop//CodeOfGitee//r-code//2207124039_毛若沣_02//additon1.xls")

# 读取addition2.xls文件
addition2 <- read_excel("C://Users//86189//Desktop//CodeOfGitee//r-code//2207124039_毛若沣_02//addition2.xls")

# 9. 将leadership与additon1数据框合并
merged_data1 <- merge(leadership, additon1, by = "manager")

# 10. 将leadership与addition2数据框合并
# 合并数据框，即使没有匹配的行也会保留leadership中的所有行

merged_data2 <- merge(leadership, addition2, by = "manager", all.x = TRUE)
merged_data2<-rbind(leadership,addition2)

# 11. 将leadership中manager、age、seniority变量中的数据生成新的数据框selectdata
selectdata <- leadership %>% select(manager, age, seniority)

# 12. 生成新数据框selectdata2，包含leadership中除entry外的所有变量
selectdata2 <- leadership %>% select(-entry)

# 13. 选择leadership中所有30岁以上的男性存入数据框newdata2
newdata2 <- leadership %>% filter(age > 30 & gender == "M")
